/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.web.service;

import com.web.dao.*;
import com.web.daoimpl.BaseDaoImpl;
import com.web.entity.Grade;
import com.web.entity.Test;
import com.web.util.DatabaseUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * dao接口的实现类
 *
 * @author liqianru
 */
public class Testdaoimpl  extends BaseDaoImpl implements Testdao {

    @Override
    public Test getoneQbyQid(String qid) {
        String rowname = "QID";
        Test test = new Test();
        test = (Test) Basedata.datadml(rowname, qid);
        return test;
    }

    @Override
    public List<Test> getAllbyCid(String cid) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rset = null;
        List<Test> list = new ArrayList();
        try {
            con = DatabaseUtil.getConnection();
            ps = con.prepareStatement("select *from TestQuestions where Subject=?");
            ps.setString(1, cid);
            rset = ps.executeQuery();
            while (rset.next()) {
                list.add(new Test(
                        rset.getString(1),
                        rset.getString(2),
                        rset.getString(3),
                        rset.getString(4),
                        rset.getString(5),
                        rset.getString(6),
                        rset.getInt(7),
                        rset.getString(8)
                ));
            }

        } catch (SQLException ex) {
            Logger.getLogger(Testdaoimpl.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DatabaseUtil.close(rset, ps, con);
        }
        return list;
    }

    @Override
    public List<Test> getAllBypage(int currentPage, int pageSize) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rset = null;
        List<Test> list = new ArrayList();
        try {
            con = DatabaseUtil.getConnection();
            ps = con.prepareStatement("select * from TestQuestions order by Tqid OFFSET ? ROWS FETCH next ? rows only");////xuyaobiaozhu
            ps.setInt(1, ((currentPage - 1) * pageSize));
            ps.setInt(2, pageSize);
            rset = ps.executeQuery();
            while (rset.next()) {
                list.add(new Test(
                        rset.getString(1),
                        rset.getString(2),
                        rset.getString(3),
                        rset.getString(4),
                        rset.getString(5),
                        rset.getString(6),
                        rset.getInt(7),
                        rset.getString(8)
                ));
            }

        } catch (SQLException ex) {
            Logger.getLogger(Testdaoimpl.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DatabaseUtil.close(rset, ps, con);
        }
        return list;
    }

    @Override
    public List<Test> getAll() {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rset = null;
        List<Test> list = new ArrayList();
        try {
            con = DatabaseUtil.getConnection();
            ps = con.prepareStatement("select * from TestQuestions");
            rset = ps.executeQuery();
            while (rset.next()) {
                list.add(new Test(
                        rset.getString(1),
                        rset.getString(2),
                        rset.getString(3),
                        rset.getString(4),
                        rset.getString(5),
                        rset.getString(6),
                        rset.getInt(7),//scorce
                        "isnull",//cid
                        rset.getInt(9),//tqid
                        rset.getInt(10),//courseid
                        rset.getString(8)//coursename
                        
                ));
            }

        } catch (SQLException ex) {
            Logger.getLogger(Testdaoimpl.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DatabaseUtil.close(rset, ps, con);
        }
        return list;
    }

    @Override
    public List<Test> getBykeyword(String keyword) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rset = null;
        List<Test> list = new ArrayList();
        try {
            con = DatabaseUtil.getConnection();
            ps = con.prepareStatement("select * from TestQuestions where Topic like ? or A like ? or B like ? or C like ? or D like ?");
            ps.setString(1, "%" + keyword + "%");
            ps.setString(2, "%" + keyword + "%");
            ps.setString(3, "%" + keyword + "%");
            ps.setString(4, "%" + keyword + "%");
            ps.setString(5, "%" + keyword + "%");
            rset = ps.executeQuery();
            while (rset.next()) {
                list.add(new Test(
                       rset.getString(1),
                        rset.getString(2),
                        rset.getString(3),
                        rset.getString(4),
                        rset.getString(5),
                        rset.getString(6),
                        rset.getInt(7),//scorce
                        "isnull",//cid
                        rset.getInt(9),//tqid
                        rset.getInt(10),//courseid
                        rset.getString(8)//coursename
                ));
            }

        } catch (SQLException ex) {
            Logger.getLogger(Testdaoimpl.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DatabaseUtil.close(rset, ps, con);
        }
        return list;

    }

    @Override
    public int insertTest(Test t) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rset = null;
        con = DatabaseUtil.getConnection();
        int rows = 0;
        try {
            con = DatabaseUtil.getConnection();
            ps = con.prepareStatement("insert into TestQuestions values(?,?,?,?,?,?,?,?,?,?)");
            ps.setString(1, t.getTopic());
            ps.setString(2, t.getQestionA());
            ps.setString(3, t.getQestionB());
            ps.setString(4, t.getQestionC());
            ps.setString(5, t.getQestionD());
            ps.setString(6, t.getAnswer());
            ps.setInt(7, t.getScore());
            ps.setString(8, t.getCoursename());
            ps.setInt(9, t.getQid());
            ps.setInt(10, t.getCourseid());
            rows = ps.executeUpdate();

        } catch (SQLException ex) {
            Logger.getLogger(Testdaoimpl.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DatabaseUtil.close(rset, ps, con);
        }
        return rows;
    }

    @Override 
    public int modifyTest(Test t) {
        int[] i=new int[]{t.getQid()};
      
        return update("update  TestQuestions set Topic=?,A=?,B=?,C=?,D=?,RightAnswer=? where Tqid=?",i,t.getTopic(),t.getQestionA(),t.getQestionB(),t.getQestionC(),t.getQestionD(),t.getAnswer() );
    }

    @Override
    public int deleteTest(String qid) {
        return excuteUpdate("delete from TestQuestions where Tqid=?", qid);
    }

    @Override
    public List<Test> getOneByCourseid(int courseid) {

        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Test> list = new ArrayList<>();

        try {
            con = DatabaseUtil.getConnection();
            ps = con.prepareStatement("select Topic,A,B,C,D,Tqid,coursename,RightAnswer,score from TestQuestions where CourseID=?");
            ps.setInt(1, courseid);
            rs = ps.executeQuery();
            while (rs.next()) {
                list.add(new Test(rs.getString(1), rs.getString(2), rs.getString(3),rs.getString(4),rs.getString(5),rs.getInt(6),rs.getString(7),rs.getString(8),rs.getInt(9)));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.close(rs, ps, con);
        }
    return list;
        }

    @Override
    public List<Grade> getOneStudentgradeBysid(String sid) {
         Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Grade> list = new ArrayList<>();

        try {
            con = DatabaseUtil.getConnection();
            ps = con.prepareStatement("select * from Grades where Sid=? order by Grade DESC");
            ps.setString(1, sid);
            rs = ps.executeQuery();
            while (rs.next()) {
                list.add(new Grade(rs.getString(1), rs.getString(2), rs.getString(3),rs.getInt(4)));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.close(rs, ps, con);
        }
    return list;
    }

}
